﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Sql;
using System.Data.Common;
using System.Data.SqlClient;

namespace sccf
{
    public class GestionarUsuariosModel
    {
        public static SqlDataReader ObtenerPersonas(string elemento, string tipo)
        {
            SqlConnection connection = new SqlConnection(Vars.connectionString);
            connection.Open();
            SqlParameter Tipo = new SqlParameter("@tipo", tipo);
            SqlParameter Elemento = new SqlParameter("@elemento", elemento);

            SqlCommand cmd = new SqlCommand("ObtenerPersonas", connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(Tipo);
            cmd.Parameters.Add(Elemento);

            SqlDataReader result = cmd.ExecuteReader();
            return result;
        }

        public static void BorrarPersonas(string id)
        {
            SqlConnection connection = new SqlConnection(Vars.connectionString);
            connection.Open();
            
            SqlParameter ID = new SqlParameter("@id", id);

            SqlCommand cmd = new SqlCommand("BorrarPersonas", connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(ID);
            
            cmd.ExecuteReader();
        }

        public static void Registrar(string login, string nombre, int tipo, string correo1, string correo2, string cargo, string pass)
        {
            SqlConnection connection = new SqlConnection(Vars.connectionString);
            connection.Open();

            SqlParameter ID = new SqlParameter("@id", login);
            SqlParameter NOMBRE = new SqlParameter("@Nomb", nombre);
            SqlParameter TIPO = new SqlParameter("@TipoUsuario", tipo);
            SqlParameter CORREO1 = new SqlParameter("@Email", correo1);
            SqlParameter CORREO2 = new SqlParameter("@Email2", correo2);
            SqlParameter CARGO = new SqlParameter("@Cargo", cargo);
            SqlParameter PASS = new SqlParameter("@pass", pass);

            SqlCommand cmd = new SqlCommand("RegistrarPersonas", connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(ID);
            cmd.Parameters.Add(NOMBRE);
            cmd.Parameters.Add(TIPO);
            cmd.Parameters.Add(CORREO1);
            cmd.Parameters.Add(CORREO2);
            cmd.Parameters.Add(CARGO);
            cmd.Parameters.Add(PASS);

            cmd.ExecuteReader();
            string tipo2;
            if (tipo==1)
                tipo2="AdmConsejo";
            else if (tipo==2)
                tipo2="AsistConsejo";
            else
                tipo2="consejo";
            ConexionLDAP.agregarGrupo(login, tipo2);
        }

        public static SqlDataReader BuscarTodos()
        {
            SqlConnection connection = new SqlConnection(Vars.connectionString);
            connection.Open();

            SqlCommand cmd = new SqlCommand("ObtenerTodasPersonas", connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            
           SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        public static SqlDataReader ModificarPersona(string id, string nombre, string cargo, string email, string email2)
        {
            SqlConnection connection = new SqlConnection(Vars.connectionString);
            connection.Open();
            SqlParameter Id = new SqlParameter("@id", id);
            SqlParameter Nombre = new SqlParameter("@nombre", nombre);
            SqlParameter Cargo = new SqlParameter("@cargo", cargo);
            SqlParameter Email = new SqlParameter("@email", email);
            SqlParameter Email2 = new SqlParameter("@email2", email2);

            SqlCommand cmd = new SqlCommand("ModificarPersonas", connection);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(Id);
            cmd.Parameters.Add(Nombre);
            cmd.Parameters.Add(Cargo);
            cmd.Parameters.Add(Email);
            cmd.Parameters.Add(Email2);

            SqlDataReader result = cmd.ExecuteReader();
            return result;
        }
    }
}